<html>
<head>
  <title>Evernote Export</title>
  <basefont face="微软雅黑" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <meta name="exporter-version" content="YXBJ Windows/603139 (zh-CN, DDL); Windows/10.0.0 (Win64); EDAMVersion=V2;"/>
  <style>
    body, td {
      font-family: 微软雅黑;
      font-size: 10pt;
    }
  </style>
</head>
<body>
<a name="576"/>

<div>
<span><div><div>什么是分库分表: 通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库 （主机）上面，以达到分散单台设备负载的效果</div><div>分库分表解决的问题: 分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原来单体服务的数据 库进行拆分.将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从 而达到提升数据库性能的目的。</div><div>什么情况下需要分库分表:1. 单机存储容量遇到瓶颈.   2. 连接数,处理能力达到上限.</div><div>注意: 分库分表之前,要根据项目的实际情况 确定我们的数据量是不是够大,并发量是不是够大,来决定是 否分库分表. 数据量不够就不要分表,单表数据量超过1000万或100G的时候, 速度就会变慢</div><div>分库分表包括： 垂直分库、垂直分表、水平分库、水平分表 四种方式。</div><div>垂直分库: 数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进行分类,分布到不同的 数据库上面: 将数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果</div><div>垂直分表: 将一个表按照字段分成多表，每个表存储其中一部分字段。</div><div>优点：</div><ol><li><div>解决业务层面的耦合，业务清晰</div></li><li><div>能对不同业务的数据进行分级管理、维护、监控、扩展等</div></li><li><div>高并发场景下，垂直分库一定程度的提高访问性能</div></li></ol><div>ps: 垂直拆分没有彻底解决单表数据量过大的问题</div><div>水平分库</div><ol><li><div>将单张表的数据切分到不同的数据库中，每个数据库具有相同的库与表，只是表中数据集合不同。</div></li><li><div>简单讲就是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主 机）上面, 例如将订单表 按照id是奇数还是偶数, 分别存储在不同的库中</div></li></ol><div>水平分表:</div><ol><li><div>针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。 但是 这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。</div></li></ol><div><br/></div><div><br/></div><div>如何实现分库分表:mycat, sharding jdbc</div><div>mycat:<span style="font-size: unset; color: unset; font-family: unset;">最流行的基于 java 语言编写的数据库中间件，是一个实现了 MySQL 协议的服务器，前 端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功 能是分库分表和读写分离，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他 数据库里。</span></div><div>MyCat支持的数据库: Oracle MySQL  mongoDB SQlServer</div><div><br/></div><div>MyCat的分片策略</div><div>什么是分片 : 通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。</div><div>MyCat支持两种切分模式:</div><ol><li><div>一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切可以称之 为数据的垂直（纵向）切分</div></li><li><div>另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数 据库（主机）上面，这种切分称之为数据的水平（横向）切分。</div></li></ol><div><br/></div><div>逻辑库(schema): 对数据进行分片处理之后，从原有的一个库，被切分为多个分片数据库，所有的分片数据库集群构 成了整个完整的数据库存储。Mycat在操作时，使用逻辑库来代表这个完整的数据库集群，便于对 整个集群操作。</div><div>逻辑表(table): 既然有逻辑库，那么就会有逻辑表，分布式数据库中，对应用来说，读写数据的表就是逻辑表。 逻辑表，可以是数据切分后，分布在一个或多个分片库中，也可以不做数据切分，不分片，只有一 个表构成。</div><div><br/></div><div>分片节点(dataNode): 数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点 (dataNode）。</div><div>节点主机(dataHost): 数据切分后，每个分片节点不一定都会独占一台机器，同一机器上面可以有多个分片数据库， 这 样一个或多个分片节点所在的机器就是节点主机，为了规避单节点主机并发数限制， 尽量将读写 压力高的分片节点均衡的放在不同的节点主机dataHost。 </div><div>分片规则:一个大表被分成若干个分片表，就需要一定的规则rule，这样按照某种业务规 则把数据分到 某个分片的规则就是分片规则，数据切分选择合适的分片规则非常重要，将极大的 避免后续数据处理的难度。</div><div><br/></div><div><br/></div><div>全局序列号： 在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式。</div><div>MyCat读写分离</div><div>读写分离： 在实际的生产环境中, 数据的读写操作如果都在同一个数据库服务器中进行, 当遇到大量的并发读或者写 操作的时候,是没有办法满足实际需求的,数据库的吞吐量将面临巨大的瓶颈压力.</div><div>主从复制 通过搭建主从架构, 将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处 理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。</div><div>读写分离 读写分离就是让主库处理事务性操作，从库处理select查询。数据库复制被用来把事务性查询导致 的数据变更同步到从库，同时主库也可以select查询。</div><div>读写分离的数据节点中的数据内容是一致。</div><div>MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制架 构。</div><div>主从复制的用途</div><ol><li><div>实时灾备，用于故障切换</div></li><li><div>读写分离，提供查询服务</div></li><li><div>备份，避免影响业务</div></li></ol><div>主从部署必要条件</div><ol><li><div>主库开启binlog日志（设置log-bin参数）</div></li><li><div>主从server-id不同</div></li><li><div>从库服务器能连通主库</div></li></ol><div>主从复制的原理</div><ol><li><div>Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。</div></li><li><div>主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的 数据就和主服务器上的数据相同了。</div></li><ol><li><div>主库db的更新事件(update、insert、delete)被写到binlog</div></li><li><div>主库创建一个binlog dump thread，把binlog的内容发送到从库</div></li><li><div>从库启动并发起连接，连接到主库</div></li><li><div>从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log</div></li><li><div>从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内 容写入到slave的db</div></li></ol></ol><div><br/></div><div>主从复制架构搭建： Mysql的主从复制至少是需要两个Mysql的服务，当然Mysql的服务是可以分布在不同的服务器上，也可 以在一台服务器上启动多个服务。</div><ol><li><div>master中创建数据库和表</div></li><li><div>修改主数据库的配置文件my.</div></li><li><div>重启MySQL</div></li><li><div>在主数据库上, 创建一个允许从数据库来访问的用户账号</div></li><li><div>停止主数据库的更新操作, 并且生成主数据库的备份</div></li><li><div>导出数据库,恢复写操作</div></li><li><div>将刚才主数据库备份的test.sql导入到从数据库</div></li><li><div>接着修改从数据库的 my.cnf</div></li><li><div>在从数据库设置相关信息</div></li><li><div>修改auto.cnf中的UUID,保证唯一</div></li><li><div>在从服务器上,启动slave 进程</div></li><li><div>在我们的主服务器做一些更新的操作,然后在从服务器查看是否已经更新</div></li></ol><div>实现读写分离： 数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于 MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取 决于系统的压力，通常是1-3个读节点的配置</div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div>ShardingJDBC</div><div>分库分表带来的问题： 关系型数据库在单机单库的情况下,比较容易出现性能瓶颈问题,分库分表可以有效的解决这方面的问 题,但是同时也会产生一些 比较棘手的问题.</div><ol><li><div>事务一致性问题： 当我们需要更新的内容同时分布在不同的库时, 不可避免的会产生跨库的事务问题. 原来在一个数据库操 作, 本地事务就可以进行控制, 分库之后 一个请求可能要访问多个数据库,如何保证事务的一致性,目前还 没有简单的解决方案</div></li><li><div>跨节点关联的问题： 在分库之后, 原来在一个库中的一些表,被分散到多个库,并且这些数据库可能还不在一台服务器,无法关联 查询. 解决这种关联查询,需要我们在代码层面进行控制,将关联查询拆开执行,然后再将获取到的结果进行拼装.</div></li><li><div>分页排序查询的问题： 分库并行查询时,如果用到了分页 每个库返回的结果集本身是无序的, 只有将多个库中的数据先查出来,然 后再根据排序字段在内存中进行排序,如果查询结果过大也是十分消耗资源的.</div></li><li><div>主键避重问题： 在分库分表的环境中,表中的数据存储在不同的数据库, 主键自增无法保证ID不重复, 需要单独设计全局主 键.</div></li><li><div>公共表的问题： 不同的数据库,都需要从公共表中获取数据. 可以在每一个库都创建这个公共表, 所有对公共表的更新操 作,都同时发送到所有分库执行. ShardingJDBC可以帮助我们解决这个问题.</div></li></ol><div><br/></div><div>ShardingJDBC：</div><div>Sharding-JDBC 定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据 库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种 ORM框架的使用。</div><ol><li><div>适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使 用JDBC。</div></li><li><div>基于任何第三方的数据库连接池，如：DBCP, C3P0, Druid等。</div></li><li><div>支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。</div></li></ol><div>主要功能： 数据分片 读写分离</div><div>通过Sharding-JDBC，应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源，而不用关 心数据源的数量以及数据如何分布。</div><div><br/></div><div>Sharding-JDBC与MyCat的区别</div><ol><li><div>mycat是一个中间件的第三方应用，sharding-jdbc是一个jar包</div></li><li><div>使用mycat时不需要修改代码，而使用sharding-jdbc时需要修改代码</div></li><li><div>Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库，而 Sharding-JDBC 是基于 JDBC 的扩展，是以 jar 包的形式提供轻量级服务的。</div></li></ol><div>使用sharding-jdbc 对数据库中水平拆分的表进行操作,通过sharding-jdbc对分库分表的规则进行配 置,配置内容包括：数据源、主键生成策略、分片策略等。</div><div><br/></div><div>配置主键生成策略： 使用shardingJDBC提供的主键生成策略,全局主键 为避免主键重复, 生成主键采用 SNOWFLAKE 分布式ID生成算法</div><div>配置分片算法： {order_id % 2 + 1} 结果是偶数 操作 pay_order_1表 {order_id % 2 + 1} 结果是奇数 操作 pay_order_2表</div><div>打开SQL日志</div><div><br/></div><div>ShardingJDBC执行流程</div><ol><li><div>SQL解析: 编写SQL查询的是逻辑表, 执行时 ShardingJDBC 要解析SQL ,解析的目的是为了找到需 要改写的位置.</div></li><li><div>SQL路由: SQL的路由是指 将对逻辑表的操作,映射到对应的数据节点的过程. ShardingJDBC会获取 分片键判断是否正确,正确 就执行分片策略(算法) 来找到真实的表</div></li><li><div>SQL改写: 程序员面向的是逻辑表编写SQL, 并不能直接在真实的数据库中执行,SQL改写用于将逻辑 SQL改为在真实的数据库中可以正确执行的SQL.</div></li><li><div>SQL执行: 通过配置规则 pay_order_$-&gt;{order_id % 2 + 1} ,可以知道当 order_id 为偶数时 , 应该向 pay_order_1表中插入数据, 为奇数时向 pay_order_2表插入数据.</div></li><li><div>将所有真正执行sql的结果进行汇总合并，然后返回。</div></li></ol><div><br/></div><div><br/></div><div><br/></div><div>什么是公共表： 公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属 于此类型。 可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。接下来看一下如 何使用Sharding-JDBC实现公共表的数据维护。</div><div>Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。它提 供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。</div><div>为了实现Sharding-JDBC的读写分离，首先，要进行mysql的主从同步配置。</div><div><br/></div><div><br/></div><div>MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接层、 服务层、引擎层和文件系统层。</div><div><br/></div><div>SQL查询流程</div><ol><li><div>通过客户端/服务器通信协议与 MySQL 建立连接</div></li><li><div>查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查 询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到 完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。</div></li><li><div>预处理器生成新的解析树。</div></li><li><div>查询优化器生成执行计划。</div></li><li><div>查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结 果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。</div></li></ol><div>MySQL物理文件包括：日志文件，数据文件，配置文件</div><div>日志文件包括 error log 错误日志 排错 /var/log/mysqld.log【默认开启】 bin log 二进制日志 备份 增量备份 DDL DML DCL Relay log 中继日志 复制 接收 replication master slow log 慢查询日志 调优 查询时间超过指定值</div><div><br/></div><div><br/></div><div>MySQL数据备份：</div><div><br/></div><div>数据库备份的应用场景</div><ol><li><div>数据丢失应用场景</div></li><ol><li><div>系统硬件或软件故障</div></li><li><div>自然灾害,比如水灾 火灾 地震等</div></li><li><div>黑客攻击,非法访问者故意破坏</div></li><li><div>误操作 , 人为的误操作占比最大</div></li></ol><li><div>非数据丢失应用场景：</div></li><ol><li><div>开发测试环境数据库搭建</div></li><li><div>数据库或者数据迁移</div></li></ol></ol><div><br/></div><div>数据备份的类型</div><ol><li><div>按照业务方式分</div></li><ol><li><div>完全备份：将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储 位置以及数据库中的全部对象和相关信息。</div></li><li><div>差异备份： 备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件 以及数据库中其他被修改的内容。</div></li><li><div>增量备份： 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增 加或者被修改的文件。</div></li></ol><li><div>备份的组合方式</div></li><ol><li><div>完全备份与差异备份: 以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。如果在星期 六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份。 这种策略备份数据需要较多的时间，但还原数据使用较少的时间。</div></li><li><div>完全备份与增量备份: 以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。如果在星期六数 据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份。 这种策略备份数据需要较少的时间，但还原数据使用较长的时间。</div></li></ol></ol><div>MySQL冷备份和热备份: 冷备份和热备份指的是, 按照数据库的运行状态分类</div><div>冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线</div><div>有点：</div><ol><li><div>是操作比较方便的备份方法（只需拷贝文件）</div></li><li><div>低度维护，高度安全。</div></li></ol><div>缺点：</div><ol><li><div>在实施备份的全过程中，数据库必须要作备份而不能作其它工作。</div></li><li><div>若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢。</div></li><li><div>不能按表或按用户恢复。</div></li></ol><div>热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执 行一遍备份的sql语句。</div><div>优点：</div><ol><li><div>可在表空间或数据文件级备份，备份时间短。</div></li><li><div>备份时数据库仍可使用。</div></li><li><div>可达到秒级恢复（恢复到某一时间点上）。</div></li></ol><div>缺点：</div><ol><li><div>不能出错，否则后果严重。</div></li><li><div>因难维护，所以要特别仔细小心，不允许“以失败而告终”。</div></li></ol><div>MySQL查询和慢查询日志分析</div><div><br/></div><div>SQL性能下降的原因 在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿 到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分 析，执行性能下降可以体现在以下两个方面：</div><ol><li><div>等待时间长： 锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理</div></li><li><div>执行时间长</div></li><ol><li><div>查询语句写的烂</div></li><li><div>索引失效</div></li><li><div>关联查询太多join</div></li><li><div>服务器调优及各个参数的设置</div></li></ol></ol><div>需要遵守的优化原则： 查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句 的执行顺序、索引以及统计信息的采集等等方面.</div><div><br/></div><div>编写SQL的关键原则</div><ol><li><div>只返回需要的结</div></li><ol><li><div>一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行</div></li><li><div>避免使用 select * from , 因为它表示查询表中的所有字段</div></li></ol><li><div>确保查询使用了正确的索引</div></li><ol><li><div>经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；</div></li><li><div>将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；</div></li><li><div>多表连接查询的关联字段建立索引，可以提高连接查询的性能；</div></li><li><div>将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。</div></li></ol><li><div>第三条: 避免让索引失效</div></li><ol><li><div>在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效</div></li><li><div>使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引</div></li><li><div>如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL</div></li></ol></ol><div><br/></div><div>SQL的执行顺序</div><ol><li><div>FORM子句 : 左右两个表的笛卡尔积</div></li><li><div>ON: 筛选满足条件的数据</div></li><li><div> JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行</div></li><li><div>WHERE： 对不满足条件的行进行移除, 并且不能恢复 </div></li><li><div>GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值</div></li><li><div>HAVING： 对分组后的数据进行筛选</div></li><li><div>SELECT： 执行select操作，获取需要的列。</div></li><li><div>DISTINCT： 去重</div></li><li><div>ORDER BY: 排序</div></li><li><div>LIMIT：取出指定行的记录, 并将结果返回。</div></li></ol><div>JOIN查询的七种方式: 可以分为四类: 内连接 、左连接 、右连接、 全连接</div><div>慢查询: MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间 超过阈值的语句。 默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影 响。 慢查询日志支持将日志记录写入文件和数据库表。</div><div><br/></div><div>MySQL存储引擎:</div><div>存储引擎： 存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方 法。就像汽车的发动机一样, 存储引擎好坏 决定的数据库提供的功能和性能</div><div>作用：<span style="color: unset; font-family: unset; font-size: unset;">并发性</span> 事务支持 引用完整性 索引支持</div><div>常见的3种存储引擎</div><ol><li><div>InnoDB(推荐)： InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大 的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用 InnoDB是最理想的选择</div></li><ol><li><div>优点</div></li><ol><li><div>nnodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别</div></li><li><div>支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快。</div></li><li><div>支持自增长列。 支持外键。</div></li><li><div>适合于大容量数据库系统，支持自动灾难恢复。</div></li></ol><li><div>缺点： 它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表</div></li><li><div>应用场景</div></li><ol><li><div>当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表， 所以在并发较高时，使用Innodb引擎会提升效率</div></li><li><div>更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求。</div></li></ol></ol><li><div>MyISAM： MyISAM引擎, 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select， insert为主的应用基本上可以用这个引擎来创建表</div></li><ol><li><div>优点</div></li><ol><li><div>MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务 器。</div></li><li><div>MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点</div></li><li><div>另外进行大批量插入操作时执行速度也比较快。</div></li></ol><li><div>缺点</div></li><ol><li><div>MyISAM表没有提供对数据库事务的支持。</div></li><li><div>不支持行级锁和外键。</div></li><li><div>不适合用于经常UPDATE（更新）的表，效率低</div></li></ol><li><div>应用场景</div></li><ol><li><div>以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务。</div></li><li><div>对数据一致性要求不是非常高的业务（不支持事务）</div></li><li><div>硬件资源比较差的机器可以用 MyiSAM （占用资源少）</div></li></ol></ol><li><div>MEMORY： MEMORY的特点是 将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的纬度表</div></li><ol><li><div>优点 memory类型的表访问非常的快，因为它的数据是放在内存中的</div></li><li><div>缺点</div></li><ol><li><div>一旦服务关闭，表中的数据就会丢失掉。</div></li><li><div>只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式</div></li></ol><li><div>应用场景</div></li><ol><li><div>目标数据较小，而且被非常频繁地访问。</div></li><li><div>如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。</div></li><li><div>存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。</div></li></ol></ol></ol><div><br/></div><div>.MySQL索引优化：</div><div>索引： 索引就是排好序的,帮助我们进行快速查找的数据结构. 简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高 数据查询的效率，从而提升服务器的性能. 专业一点来说呢，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行 的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用 扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。</div><div>没有用索引时执行 select * from where t.Col2 = , 数据从磁盘一条一条拿去最终找到结果， 效率低下。</div><div>为了加快查找,可以维护一个二叉树,左侧节点小于父节点, 右侧节点大于父节点,每个节点分别保存 字段数据和一个指向对应数据记录物理地址的指针.</div><div>查找时 就可以使用二叉树查找获取相应的数据,从而快速检索出符合条件的记录， 一般来说索引本身也比较大,不可能全部保存在内存中,因此索引通常是以索引文件的形式存储在磁 盘上</div><div>索引的种类</div><ol><li><div>普通索引： 这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。</div></li><li><div>唯一索引： 与&quot;普通索引&quot;类似，不同的就是：索引字段的值必须唯一，但允许有空值 。</div></li><li><div>主键索引： 它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只 能有一个主键。</div></li><li><div>复合索引： 用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替 多个单一索引，相比多个单一索引复合索引所需的开销更小。</div></li><ol><li><div>复合索引使用注意事项：</div></li><ol><li><div>何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对 更新操作效率有很大影响。</div></li><li><div>如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索 引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提 高。</div></li></ol></ol><li><div>全文索引</div></li><ol><li><div>查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很 低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有 MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。</div></li><li><div>全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE 两种</div></li><li><div>和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，</div></li><li><div>全文索引使用注意事项：</div></li><ol><li><div>全文索引必须在字符串、文本字段上建立。</div></li><li><div>全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84； myisam：4-84）</div></li></ol></ol></ol><div><br/></div><div>索引的优势与劣势</div><div>优势：</div><ol><li><div>提高数据检索的效率,降低数据库的IO成本</div></li><li><div>通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗</div></li></ol><div><br/></div><div>劣势</div><ol><li><div>创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加</div></li><li><div>索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间</div></li><li><div>当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速 度</div></li></ol><div><br/></div><div>创建索引的原则</div><ol><li><div>在经常需要搜索的列上创建索引，可以加快搜索的速度；</div></li><li><div>在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；</div></li><li><div>在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；</div></li><li><div>在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续 的；</div></li><li><div>在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快 排序查询时间；</div></li><li><div>在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。</div></li></ol><div><br/></div><div>索引原理： MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。</div><div><br/></div><div>HASH结构</div><ol><li><div>Hash底层实现是由Hash表来实现的，是根据键值 存储数据的结构。非常适合根据 key查找value值，也就是单个key查询，或者说等值查询。</div></li><ol><li><div>对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值， 并且不同键值的行计算出来的哈希码也不一样。</div></li></ol><li><div>Hash索引的缺点</div></li><ol><li><div>哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取 行。</div></li><li><div>哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。</div></li><li><div>哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。</div></li></ol><li><div>Hsah索引的优点</div></li><ol><li><div>只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引</div></li><li><div>访问哈希索引的数据非常快，除非有很多哈希冲突。</div></li></ol></ol><div><br/></div><div><br/></div><div>B+Tree结构： MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。</div><ol><li><div>非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值</div></li><li><div>叶子节点包含了所有的索引值和data数据</div></li><li><div>叶子节点用指针连接，提高区间的访问性能</div></li></ol><div>B树索引的应用</div><ol><li><div>全键值查询 where x=123</div></li><li><div>键值范围查询 where 45 &lt; x &lt; 123</div></li></ol><div><span style="color: unset; font-family: unset; font-size: unset;"> </span></div><div><span style="color: unset; font-family: unset; font-size: unset;">EXPLAIN字段：</span> explain 关键字可以模拟优化器执行 sql 语句，从而知道 mysql 是如何处理 sql 语句 的,方便我们开发人员有针对性的对SQL进行优化.</div><div><br/></div><div>Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息</div><div><br/></div><div>MySQL锁机制</div><div>概念： 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有 序所设计的一种规则</div><div>MySQL的锁分类： MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁机 制。</div><ol><li><div>MyISAM和MEMORY存储引擎采用的表级锁</div></li><li><div>InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。</div></li><li><div>BDB采用的是页面锁，也支持表级锁</div></li></ol><div>按照数据操作的类型分</div><ol><li><div>读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。</div></li><li><div>写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。</div></li></ol><div><br/></div><div>按照数据操作的粒度分</div><ol><li><div>表级锁：开销小，加锁快；不会出现死锁；<span style="color: unset; font-family: unset; font-size: unset;">锁定粒度大，发生锁冲突的概率最高，并发度最低。</span></div></li><li><div><span style="color: unset; font-family: unset; font-size: unset;">行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最 高。</span></div></li><li><div><span style="color: unset; font-family: unset; font-size: unset;">页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发 度一般</span></div></li></ol><div><br/></div><div>按照操作性能可分为乐观锁和悲观锁</div><ol><li><div>乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测， 如果发现冲突了，则提示错误信息。</div></li><li><div>悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改 的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。</div></li></ol><div><br/></div><div>表级锁(偏读)</div><ol><li><div>表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消 耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。</div></li><li><div>表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。</div></li><li><div>特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。</div></li></ol><div><br/></div><div>行级锁(偏写): 行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性 能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。</div><ol><li><div>使用MySQL行级锁的两个前提</div></li><ol><li><div>使用 innoDB 引擎</div></li><li><div>开启事务 (隔离级别为 Repeatable Read )</div></li></ol><li><div>InnoDB行锁的类型</div></li><ol><li><div>共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能 对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。</div></li><li><div>排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的 封锁。获准排他锁的事务既能读数据，又能修改数据。</div></li></ol><li><div>加锁的方式</div></li><ol><li><div>InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁， select语句默认不会加任何锁类型，如果要加可以使用下面的方式:</div></li><li><div>加共享锁（S）：select * from table_name where ... lock in share mode;</div></li><li><div>加排他锁（x）：select * from table_name where ... for update;</div></li></ol><li><div>锁兼容</div></li><ol><li><div>共享锁只能兼容共享锁, 不兼容排它锁</div></li><li><div>排它锁互斥共享锁和其它排它锁</div></li></ol><li><div>行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把 整张表锁住，这点需要咱们格外的注意</div></li></ol><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/></div><div><br/><br/></div><div><br/></div></div><div><br/></div></span>
</div></body></html> 